﻿CREATE PROCEDURE [dbo].[GoodsVsContragentsUpdateEx]
	@GoodsId [int],
	@ContragentId [int],
	@GoodsCode [int],
	@GoodsName [varchar](150),
	@CountryName [varchar](20),
	@ManufacturerName [varchar](70),
	@Note [varchar](250),
	@MD [datetime],
	@MW [varchar](50),
	@ToDelete [bit],
	@Original_Id [int],
	@Original_MD [datetime]
WITH EXECUTE AS CALLER
AS
DECLARE @strMsg nvarchar(500), @intRet int, @intErrCode int
DECLARE @orgContragentId int, @orgGoodsCode int, @orgToDelete bit, @orgParentToDelete bit
DECLARE @orgGoodsName varchar(150), @orgCountryName varchar(20), @orgManufacturerName varchar(70)

SET NOCOUNT ON

IF (@GoodsId Is Null) OR (@ContragentId Is Null) OR (@GoodsCode Is Null) OR (@GoodsName Is Null)
OR (@ToDelete Is Null)
BEGIN
	SELECT @intErrCode = 1, @strMsg = 'Неправильные параметры в вызове процедуры ' + Object_Name(@@Procid) + ' !'
	RAISERROR (@strMsg, 11, @intErrCode)
	RETURN @intErrCode
END

IF (ltrim(@GoodsName) = '')
BEGIN
	SELECT @intErrCode = 2, @strMsg = 'Незаполнено наименование товара у контрагента!'
	RAISERROR (@strMsg, 11, @intErrCode)
	RETURN @intErrCode
END

SELECT @orgContragentId = ContragentId, @orgGoodsCode = GoodsCode,
@orgGoodsName = GoodsName, @orgCountryName = CountryName, @orgManufacturerName = ManufacturerName,
@orgToDelete = ToDelete
FROM [CA].[GoodsVsContragents] WHERE Id = @Original_Id AND MD = @Original_MD
IF (@@rowcount <> 1)
BEGIN
	SELECT @intErrCode = 3, 
	@strMsg = 'Данные изменены после последнего запроса!'
	RAISERROR (@strMsg, 11, 1)
	RETURN @intErrCode
END

SELECT @orgParentToDelete = ToDelete
FROM CA.Goods WHERE Id = @GoodsId
IF (@@rowcount <> 1)
BEGIN
	SELECT @intErrCode = 4, 
	@strMsg = 'Отсутствует родительская запись с кодом ' + cast(@GoodsId as varchar(20)) + ' в справочнике товаров!'
	RAISERROR (@strMsg, 11, 1)
	RETURN @intErrCode
END

IF @orgParentToDelete = 1 
BEGIN
	SELECT @intErrCode = 5, 
	@strMsg = 'Родительская запись с кодом ' + cast(@GoodsId as varchar(20)) + ' в справочнике товаров отмечена для удаления!'
	RAISERROR (@strMsg, 11, 1)
	RETURN @intErrCode
END

IF @orgToDelete = 1 --Запись была отмечена для удаления
BEGIN
	IF @ToDelete = 1 
	BEGIN
		SELECT @intErrCode = 6, 
		@strMsg = 'Нельзя редактировать запись, отмеченную для удаления!'
		RAISERROR (@strMsg, 11, 1)
		RETURN @intErrCode
	END
END ELSE BEGIN
	IF @ToDelete = 1 
	BEGIN
		SELECT @intErrCode = 7, 
		@strMsg = 'Для удаления данных должна использоваться другая процедура!'
		RAISERROR (@strMsg, 11, 1)
		RETURN @intErrCode
	END
END

IF @orgContragentId <> @orgContragentId 
BEGIN
	SELECT @intErrCode = 8, 
	@strMsg = 'Нельзя менять контрагента!'
	RAISERROR (@strMsg, 11, 1)
	RETURN @intErrCode
END


DECLARE @tmp int
DECLARE @CId INT--переменная для контрагентов
SET @tmp = @ContragentId
DECLARE @tmp1 INT
DECLARE @tab TABLE (Id INT PRIMARY KEY CLUSTERED)
--находим родительский
SELECT @tmp1 = ParentContragentId FROM PR.ContragentVsContragents
WHERE ChildContragentId = @tmp
--Если нет значение, значит изначально родительский
IF (@tmp1 is null)
BEGIN 
	--SET @tmp1 = @tmp
	INSERT INTO @tab(Id) VALUES (@tmp)
	INSERT INTO @tab(Id)
	SELECT ChildContragentId FROM PR.ContragentVsContragents
	WHERE ParentContragentId = @tmp
END
ELSE
BEGIN
	INSERT INTO @tab(Id) VALUES (@tmp1)
	INSERT INTO @tab(Id)
	SELECT ChildContragentId FROM PR.ContragentVsContragents
	WHERE ParentContragentId = @tmp1
END
DECLARE @tab2 TABLE(Id INT PRIMARY KEY CLUSTERED, ContragentId INT, UNIQUE(Id,ContragentId))

INSERT INTO @tab2(Id, ContragentId)
SELECT Id, ContragentId FROM [CA].[GoodsVsContragents]
WHERE GoodsCode = @GoodsCode AND GoodsName = @GoodsName --AND MD = @Original_MD
AND ContragentId IN 
(SELECT Id FROM @tab)

DECLARE cur CURSOR FOR
SELECT Id, ContragentId  FROM @tab2
OPEN cur
FETCH NEXT FROM cur 
INTO @tmp, @CId

WHILE @@FETCH_STATUS = 0
BEGIN
	IF EXISTS (SELECT TOP 1 ID FROM [CA].[GoodsVsContragents] 
	WHERE ContragentId = @tmp AND GoodsCode = @GoodsCode 
	AND GoodsName = @GoodsName AND Id = @tmp
	AND ( (CountryName = @CountryName) OR (@CountryName Is Null AND CountryName Is Null) )
	AND ( (@ManufacturerName = ManufacturerName) OR (@ManufacturerName Is Null AND ManufacturerName Is Null) )
	AND ToDelete = 0 )
	BEGIN
		CONTINUE
	END

		UPDATE [CA].[GoodsVsContragents] SET [GoodsId] = @GoodsId, [ContragentId] = @CId, [GoodsCode] = @GoodsCode, 
		[GoodsName] = @GoodsName, [CountryName] = @CountryName, [ManufacturerName] = @ManufacturerName, 
		[Note] = @Note, [MD] = @MD, [MW] = @MW, ToDelete = @ToDelete 
		WHERE Id = @tmp;
		FETCH NEXT FROM cur 
		INTO @tmp, @CId		
END
--EXEC [dbo].[ProcessStatistic] @Note OUTPUT, @MD OUTPUT, @MW OUTPUT
	SET NOCOUNT OFF;
/*UPDATE [CA].[GoodsVsContragents] SET [GoodsId] = @GoodsId, [ContragentId] = @ContragentId, [GoodsCode] = @GoodsCode, 
[GoodsName] = @GoodsName, [CountryName] = @CountryName, [ManufacturerName] = @ManufacturerName, 
[Note] = @Note, [MD] = @MD, [MW] = @MW, ToDelete = @ToDelete 
WHERE Id = @Original_Id;*/
	
/*SELECT Id, GId, GoodsId, ContragentId, GoodsCode, GoodsName, CountryName, ManufacturerName, Note, MD, MW, ToDelete 
FROM CA.GoodsVsContragents WHERE (Id = @Original_Id)*/

RETURN 0
